package db

import (
	"database/sql"
	"errors"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"math"
	"reflect"
	"strings"
	"time"
)

type DbConnect struct {
	driver       string
	user         string
	password     string
	host         string
	port         string
	database     string
	charset      string
	maxOpenConns int
	connect      *sql.DB
}

func Open(driver string, user string, password string, host string, port string, database string, charset string, maxOpenConns int) *DbConnect {
	c := &DbConnect{}
	c.user = user
	c.password = password
	c.host = host
	c.port = port
	c.database = database
	c.charset = charset
	c.maxOpenConns = maxOpenConns

	dataSourceName := fmt.Sprintf(
		"%s:%s@tcp(%s:%s)/%s?charset=%s",
		c.user,
		c.password,
		c.host,
		c.port,
		c.database,
		c.charset,
	)
	DB, e := sql.Open(driver, dataSourceName)
	if e != nil {
		panic(e)
	}
	DB.SetConnMaxLifetime(time.Second * 10)
	DB.SetMaxOpenConns(c.maxOpenConns)
	DB.SetMaxIdleConns(int(math.Ceil(float64(c.maxOpenConns / 10))))
	c.connect = DB
	return c
}

type Db struct {
	dbConnect        *DbConnect
	tx               *sql.Tx
	transactionTimes int
}

func NewDb(dbConnect *DbConnect) *Db {
	return &Db{
		dbConnect:        dbConnect,
		tx:               nil,
		transactionTimes: 0,
	}
}

func (db *Db) Query(query string, args ...interface{}) ([]map[string]interface{}, error) {
	query, args = sqlPrepare(query, args)
	var stmt *sql.Stmt
	var e error
	if db.tx != nil {
		stmt, e = db.tx.Prepare(query)
	} else {
		stmt, e = db.dbConnect.connect.Prepare(query)
	}

	if e != nil {
		return nil, &DbError{Err: errors.New(" DB.Prepare " + e.Error()), Sql: query}
	}

	defer stmt.Close()

	rows, e := stmt.Query(args...)

	if e != nil {
		return nil, &DbError{Err: errors.New(" stmt.Query " + e.Error()), Sql: query}
	}
	defer rows.Close() // 关闭连接，不然会一直保持连接数据库

	columns, _ := rows.Columns()
	columnsLen := len(columns)
	list := make([]map[string]interface{}, 0)
	row := make([]interface{}, columnsLen)
	rowa := make([]interface{}, columnsLen)
	for key, _ := range row {
		var value interface{}
		row[key] = &value
	}

	for rows.Next() {
		_ = rows.Scan(row...)
		_ = rows.Scan(rowa...)
		data := make(map[string]interface{})
		for key, value := range row {
			val := *value.(*interface{})
			switch val.(type) {
			case []byte:
				data[columns[key]] = string(val.([]byte))
				break
			default:
				data[columns[key]] = val
			}
		}
		list = append(list, data)
	}
	return list, nil
}

func (db *Db) Find(query string, args ...interface{}) (map[string]interface{}, error) {
	query = fmt.Sprintf("%s limit 1", query)
	rows, e := db.Query(query, args...)
	if e != nil {
		return nil, e
	}

	if len(rows) > 0 {
		return rows[0], nil
	} else {
		return nil, nil
	}
}

func (db *Db) Execute(query string, args ...interface{}) (sql.Result, error) {
	query, args = sqlPrepare(query, args)
	var stmt *sql.Stmt
	var e error
	if db.tx != nil {
		stmt, e = db.tx.Prepare(query)
	} else {
		stmt, e = db.dbConnect.connect.Prepare(query)
	}

	if e != nil {
		return nil, &DbError{Err: e, Sql: query}
	}

	defer stmt.Close()

	Result, e := stmt.Exec(args...)

	if e != nil {
		return nil, &DbError{Err: e, Sql: query}
	}

	return Result, nil
}

// return error == nil commit, error != nil rollback
func (db Db) TransactionFunc(fun func() (interface{}, error)) (interface{}, error) {
	e := db.beginTransaction()
	if e != nil {
		return nil, e
	}

	res, e := fun()

	if e != nil {
		if rollbackErr := db.rollback(); rollbackErr != nil {
			return nil, fmt.Errorf("db transaction failed: %v, unable to rollback: %v", e, rollbackErr)
		}
	} else {
		if commitErr := db.commit(); commitErr != nil {
			return nil, fmt.Errorf("db transaction failed: %v, unable to commit: %v", e, commitErr)
		}
	}

	return res, e
}

func (db *Db) beginTransaction() error {
	if db.transactionTimes > 0 {
		db.transactionTimes++
		return nil
	}
	tx, e := db.dbConnect.connect.Begin()
	if e != nil {
		return e
	}
	db.tx = tx
	db.transactionTimes++
	return nil
}

func (db *Db) rollback() error {
	if db.transactionTimes == 0 {
		return errors.New("no begin transaction")
	}

	if db.transactionTimes == 1 {
		e := db.tx.Rollback()
		db.tx = nil
		return e
	}

	db.transactionTimes--
	return nil
}

func (db *Db) commit() error {

	if db.transactionTimes == 0 {
		return errors.New("no begin transaction")
	}

	if db.transactionTimes == 1 {
		e := db.tx.Commit()
		db.tx = nil
		return e
	}

	db.transactionTimes--
	return nil
}

func (db *Db) GetSql(query string, args ...interface{}) (string, []interface{}) {
	return sqlPrepare(query, args)
}

func sqlPrepare(query string, args []interface{}) (string, []interface{}) {
	nbinds := []interface{}{}
	for _, bind := range args {
		if reflect.TypeOf(bind).Kind() == reflect.Slice {
			str := ""
			for _, b := range bind.([]interface{}) {
				nbinds = append(nbinds, b)
				str += "$$,"
			}
			query = strings.Replace(query, "?", strings.Trim(str, ","), 1)
		} else {
			nbinds = append(nbinds, bind)
			query = strings.Replace(query, "?", "$$", 1)
		}
	}

	return strings.ReplaceAll(query, "$$", "?"), nbinds
}

type DbError struct {
	Err error
	Sql string
}

func (e *DbError) Error() string {
	if e.Err == nil {
		return "db error <nil>"
	}
	return fmt.Sprintf("db error: %v. (Sql:%s)", e.Err, e.Sql)
}
